package com.coderman.generateSqlBatchTools.utils;

import jxl.*;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;
import org.apache.log4j.Logger;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class JXLUtil {
    private static final Logger LOG = Logger.getLogger(JXLUtil.class);
    public static WritableFont arial14font = null;
 
    public static WritableCellFormat arial14format = null;
    public static WritableFont arial10font = null;
    public static WritableCellFormat arial10format = null;
    public static WritableFont arial12font = null;
    public static WritableCellFormat arial12format = null;
 
    public final static String UTF8_ENCODING = "UTF-8";
    public final static String GBK_ENCODING = "GBK";
 
    public static int index = 1;// 写入序号
    public static int row = 1;// 具体字段写入从第二行开始
 
    /**
     * 格式定义
     */
    public static void format() {
        try {
            arial14font = new WritableFont(WritableFont.ARIAL, 14,
                    WritableFont.BOLD);
            arial14font.setColour(jxl.format.Colour.LIGHT_BLUE);
            arial14format = new WritableCellFormat(arial14font);
            arial14format.setAlignment(jxl.format.Alignment.CENTRE);
            arial14format.setBorder(jxl.format.Border.ALL,
                    jxl.format.BorderLineStyle.THIN);
            arial14format.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);
            arial10font = new WritableFont(WritableFont.ARIAL, 10,
                    WritableFont.BOLD);
            arial10format = new WritableCellFormat(arial10font);
            arial10format.setAlignment(jxl.format.Alignment.CENTRE);
            arial10format.setBorder(jxl.format.Border.ALL,
                    jxl.format.BorderLineStyle.THIN);
            arial10format.setBackground(jxl.format.Colour.LIGHT_BLUE);
            arial12font = new WritableFont(WritableFont.ARIAL, 12);
            arial12format = new WritableCellFormat(arial12font);
            arial12format.setBorder(jxl.format.Border.ALL,
                    jxl.format.BorderLineStyle.THIN);
        } catch (WriteException e) {
            LOG.debug("格式设置错误");
            e.printStackTrace();
        }
    }
 
    public static void initExcel(String fileName, String[] colName,
            int[] widthArr) {
        JXLUtil.index = 0;// 设置为初始值。不然static的index会一直递增
        JXLUtil.row = 1;
        format();// 先设置格式
        WritableWorkbook workbook = null;
        try {
            // WorkbookSettings setEncode = new WorkbookSettings(); // 设置读文件编码
            // setEncode.setEncoding(UTF8_ENCODING);
            File file = new File(fileName);
            workbook = Workbook.createWorkbook(file);
            LOG.debug("工作环境创建成功");
            WritableSheet sheet = workbook.createSheet("Sheet 1", 0);// 建立sheet
            //sheet.mergeCells(0, 0, colName.length - 1, 0);
//            sheet.addCell((WritableCell) new Label(0, 0, fileName,
//                    arial14format));// 表头设置完成
            for (int i = 0; i < widthArr.length; i++) {
                sheet.setColumnView(i, widthArr[i]);// 设置col 宽度
            }
 
            int row = 0;
            int col = 0;
            for (col = 0; col < colName.length; col++) {
                sheet.addCell(new Label(col, row, colName[col], arial10format));// 写入
                                                                                // col名称
            }
            workbook.write();// 写入数据
            LOG.debug("init ok!");
        } catch (RowsExceededException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (WriteException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (WriteException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 多个sheet初始化方法
     * @param fileName
     * @param colName
     * @param widthArr
     * @param sheetName
     */
    public static void initExcel(String fileName, String[] colName,
                                 int[] widthArr,String [] sheetName) {
        JXLUtil.index = 0;// 设置为初始值。不然static的index会一直递增
        JXLUtil.row = 1;
        format();// 先设置格式
        WritableWorkbook workbook = null;
        try {

            File file = new File(fileName);
            workbook = Workbook.createWorkbook(file);
            LOG.debug("工作环境创建成功");

            int row = 0;

            //创建多个sheet
            List<WritableSheet> sheetList = new ArrayList<WritableSheet>();
            for(int i = 0;i < sheetName.length;i ++){
                WritableSheet sheet = workbook.createSheet(sheetName[i], i);// 建立sheet
                for (int j = 0; j < widthArr.length; j ++) {
                    sheet.setColumnView(j, widthArr[j]);// 设置col 宽度
                }
                for (int col = 0; col < colName.length; col++) {
                    sheet.addCell(new Label(col, row, colName[col], arial10format));// 写入
                    // col名称
                }
            }

            workbook.write();// 写入数据
            LOG.debug("init ok!");
        } catch (RowsExceededException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (WriteException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (WriteException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }
 
    /**
     * 将 数据写入到excel中
     * 
     * @param os
     *            创建Excel的输出流
     * @param objList
     *            要插入的数据
     * @param fieldArr
     *            字段名称
     * @param fileName
     *            excel表头名称
     * @param colName
     *            excel列明
     * @param widthArr
     *            excel单元格宽度
     */
    public static <T> void dataToExcel(ByteArrayOutputStream os,
            List<T> objList, String[] fieldArr, String fileName,
            String[] colName, int[] widthArr) {
        format();// 先设置格式
        WritableWorkbook workbook = null;
        try {
            // WorkbookSettings setEncode = new WorkbookSettings(); // 设置读文件编码
            // setEncode.setEncoding(UTF8_ENCODING);
            // File file = new File(fileName);
            workbook = Workbook.createWorkbook(os);
            LOG.debug("工作环境创建成功");
            WritableSheet sheet = workbook.createSheet("Sheet 1", 0);// 建立sheet
            sheet.mergeCells(0, 0, colName.length - 1, 0);
            sheet.addCell((WritableCell) new Label(0, 0, fileName,
                    arial14format));// 表头设置完成
            for (int i = 0; i < widthArr.length; i++) {
                sheet.setColumnView(i, widthArr[i]);// 设置col 宽度
            }
 
            int row = 1;
            int col = 0;
            for (col = 0; col < colName.length; col++) {// 写入列明
                sheet.addCell(new Label(col, row, colName[col], arial10format));// 写入
                                                                                // col名称
            }
 
            for (Object tmp : objList) {// 写入数据
                row++;
                col = 0;
                index++;
                String serialNumberStr = String.valueOf(index);
                sheet.addCell(new Label(col, row, serialNumberStr,
                        arial12format));// 第一列用来写序号
                col++;
                /**
                 * 通过反射取值，并且写入到excel中
                 */
                for (int i = 0; i < fieldArr.length; i++) {
                    String fieldName = fieldArr[i];
                    Object value = GetValueByRef.getValueByRef(tmp, fieldName);
                    String str = null;
                    if (value == null) {
                        str = "";
                    } else {
                        str = String.valueOf(value);
                    }
                    sheet.addCell(new Label(col, row, str, arial12format));
                    col++;
                }
            }
            workbook.write();// 写入数据
            LOG.debug("aaaaaaa write finished!");
        } catch (RowsExceededException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (WriteException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (WriteException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 单个sheet写入方法
     * @param objList
     * @param fileName
     * @param fieldArr
     * @param <T>
     */
    public static <T> void writeObjListToExcel(List<T> objList,
            String fileName, String[] fieldArr) {
        if (objList != null && objList.size() > 0) {
            WritableWorkbook writebook = null;
            InputStream in = null;
            try {
                /**
                 * 读取原来写入的文件
                 */
                // WorkbookSettings setEncode = new WorkbookSettings();
                // //设置读文件编码
                // setEncode.setEncoding(UTF8_ENCODING);
                in = new FileInputStream(new File(fileName));
                Workbook workbook = Workbook.getWorkbook(in);
                writebook = Workbook.createWorkbook(new File(fileName),
                        workbook);
                WritableSheet sheet = writebook.getSheet(0);
                /**
                 * 写入数据
                 */
          
                for (Object tmp : objList) {
                    int col = 0;
                    index++;
                    String serialNumberStr = String.valueOf(index);
                    sheet.addCell(new Label(col, row, serialNumberStr,
                            arial12format));// 第一列用来写序号
                    col++;
                    /**
                     * 通过反射取值，并且写入到excel中
                     */
                    for (int i = 0; i < fieldArr.length; i++) {
                        String fieldName = fieldArr[i];
                        Object value = GetValueByRef.getValueByRef(tmp,
                                fieldName);
                        String str = null;
                        if (value == null) {
                            str = "";
                        } else {
                            str = String.valueOf(value);
                        }
                        sheet.addCell(new Label(col, row, str, arial12format));
                        col++;
                    }
                    row++;
                }
                writebook.write();
                LOG.debug("报表写入成功");
            } catch (BiffException e) {
                e.printStackTrace();
            } catch (WriteException e) {
                LOG.debug("报表写入失败");
                e.printStackTrace();
            } catch (IOException e) {
                LOG.debug("io 异常");
            } finally {
                if (writebook != null) {
                    try {
                        writebook.close();
                    } catch (WriteException e) {
                        LOG.error("excel关闭异常");
                        e.printStackTrace();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }if(in != null){
                    try {
                        in.close();
                    } catch (IOException e) {
                        LOG.debug(" 文件流关闭异常");
                        e.printStackTrace();
                    }
                }
            }
 
        }
    }


    /**
     * 多个sheet写入方法
     * @param mapList
     * @param fileName
     * @param fieldArr
     * @param clazz
     * @param <T>
     */
    public static <T> void writeObjMapListToExcel(Map<String,List<T>> mapList,
                                               String fileName, String[] fieldArr,Class clazz) {
        if (mapList != null && mapList.size() > 0) {
            WritableWorkbook writebook = null;
            InputStream in = null;
            try {
                /**
                 * 读取原来写入的文件
                 */
                // WorkbookSettings setEncode = new WorkbookSettings();
                // //设置读文件编码
                // setEncode.setEncoding(UTF8_ENCODING);
                in = new FileInputStream(new File(fileName));
                Workbook workbook = Workbook.getWorkbook(in);
                writebook = Workbook.createWorkbook(new File(fileName),
                        workbook);

                /**
                 * 写入数据
                 */

                for (Map.Entry<String,List<T>> list : mapList.entrySet()) {
                    //System.out.println("key = "+list.getKey());
                    WritableSheet sheet = null;
                    if(list.getKey() != null && list.getValue() != null  && !list.getValue().isEmpty() && list.getValue().size() > 0){
                        sheet = writebook.getSheet(Integer.parseInt(list.getKey()));
                        if(sheet == null){
                            System.out.println("sheet is null ");
                        }
                        int col = 0;
                        int index = 1;
                        for(int i = 0;i < list.getValue().size();i ++){

                            sheet.addCell(new Label(col, row, (index++)+"", arial12format));// 第一列用来写序号
                            col++;

                            /**
                             * 通过反射取值，并且写入到excel中
                             */
                            for (int k = 0; k < fieldArr.length; k++) {
                                String fieldName = fieldArr[k];
                                Object value = GetValueByRef.getValueByRef(list.getValue().get(i),
                                        fieldName);
                                String str = null;
                                if (value == null) {
                                    str = "";
                                } else {
                                    str = String.valueOf(value);
                                }
                                sheet.addCell(new Label(col, row, str, arial12format));
                                col++;
                            }
                            col = 0;
                            row++;
                        }

                    }
                    row = 1;
                }
                writebook.write();
                LOG.debug("报表写入成功");
            } catch (BiffException e) {
                e.printStackTrace();
            } catch (WriteException e) {
                LOG.debug("报表写入失败");
                e.printStackTrace();
            } catch (IOException e) {
                LOG.debug("io 异常");
            } finally {
                if (writebook != null) {
                    try {
                        writebook.close();
                    } catch (WriteException e) {
                        LOG.error("excel关闭异常");
                        e.printStackTrace();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }if(in != null){
                    try {
                        in.close();
                    } catch (IOException e) {
                        LOG.debug(" 文件流关闭异常");
                        e.printStackTrace();
                    }
                }
            }

        }
    }
    
    public static void readExcel(String filePath)  
    {  
         String data2="";  
         //DateTime tem=new DateTime("");  
         SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");  
        try  
        {  
            InputStream is = new FileInputStream(filePath);  
            Workbook rwb = Workbook.getWorkbook(is);  
            //Sheet st = rwb.getSheet("0");//这里有两种方法获取sheet表,1为名字，而为下标，从0开始  
            Sheet st = rwb.getSheet("Sheet 1");  
            int rs=st.getColumns();  
            int rows=st.getRows();  
            System.out.println("列数===>"+rs+"行数："+rows);
              
             
                for(int k=0;k<rows;k++){//行  
                     for(int i=0 ;i<rs;i++){//列  
                           
                   Cell c00 = st.getCell(i,k);  
            //通用的获取cell值的方式,返回字符串  
            String strc00 = c00.getContents();  
            //获得cell具体类型值的方式  
            if(c00.getType() == CellType.LABEL)  
            {  
                LabelCell labelc00 = (LabelCell)c00;  
                strc00 = labelc00.getString();  
            }  
            //excel 类型为时间类型处理;  
            if(c00.getType()==CellType.DATE){  
                DateCell dc=(DateCell)c00;    
                strc00 = sdf.format(dc.getDate());  
                  
            }  
            //excel 类型为数值类型处理;  
            /* 
            if(c00.getType()==CellType.NUMBER|| c00.getType()==CellType.NUMBER_FORMULA){ 
                NumberCell nc=(NumberCell)c00;  
                strc00=""+nc.getValue();  
            }*/  
              
            //输出  
            System.out.println(">"+strc00);  
              
            //list.add(strc00);  
              
         
         //列，行  
//           data2=String.valueOf(st.getCell(1,k).getContents());  
//           data2=data2.replace("/", "-");  
//           java.util.Date dt=sdf.parse(data2);      
//           System.out.println(sdf.format(dt));     
//                            
                     }  
        // System.out.println(data2+"======"+list.get(k)+"=========");       
       }  
              
              
            //关闭  
            rwb.close();  
        }  
        catch(Exception e)  
        {  
            e.printStackTrace();  
        }  
    }  
 
}